﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;
using System.Windows.Forms;

namespace Einstein.Utility
{
    public class ExcelHelper
    {
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="path">存放位置</param>
        /// <param name="exceltitle">总标题</param>
        /// <param name="dt">数据行</param>
        /// <param name="colName">与数据行一一对应的列名称</param>
        /// <returns></returns>
        public static bool ExportExcel(string path, string exceltitle, DataTable dt, string[] colName)
        {
            if (dt == null)
                throw new ArgumentNullException("数据行不能为空。");

            if (colName == null)
                throw new ArgumentNullException("列名称不能为空。");

            HSSFWorkbook workbook = new HSSFWorkbook();
            int rowcount = dt.Rows.Count, colcount = colName.Length;

            ISheet sheet = workbook.CreateSheet("sheet1");

            //设置头标题
            IRow headRow = sheet.CreateRow(0);
            headRow.HeightInPoints = 30;
            ICell headCell = headRow.CreateCell(0);
            headCell.SetCellValue(exceltitle);

            ICellStyle headCellStyle = workbook.CreateCellStyle();
            headCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;

            IFont headFont = workbook.CreateFont();
            headFont.FontHeight = 20 * 20;
            headCellStyle.SetFont(headFont);
            headCell.CellStyle = headCellStyle;

            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, colcount-1));

            //设置标题行 
            IRow title = sheet.CreateRow(1);
            ICellStyle titleCellStyle = workbook.CreateCellStyle(); 

            IFont titleFont = workbook.CreateFont();
            titleFont.Boldweight = 40;
            titleCellStyle.SetFont(titleFont);
            for (int i = 0; i < colName.Length; i++)
            {
                ICell titleCell = title.CreateCell(i);
                titleCell.SetCellValue(colName[i]);
                titleCell.CellStyle = titleCellStyle;
            }

            //添加数据行  
            for (int i = 0; i < rowcount; i++)
            {
                DataRow dr = dt.Rows[i];
                IRow row = sheet.CreateRow(i + 2);
                for (int k = 0; k < colcount; k++)
                {
                    ICell cell = headRow.CreateCell(k);
                    cell.SetCellValue((string)dr[k]);
                }
            }

            //设置打印区域
           //workbook.SetPrintArea(0, "A1:G20");

            //写入文件
            using (FileStream file = new FileStream(path, FileMode.Create))
            {
                workbook.Write(file);
            }
            return true;
        }

        public static SaveFileDialog GetSaveFileDialog()
        {
            SaveFileDialog save = new SaveFileDialog();
            save.FileName = "工作簿1.xls";
            //设置文件类型 
            save.Filter = "Excel工作簿（*.xls）|*.xls"; 
            //设置默认文件类型显示顺序 
            save.FilterIndex = 1; 
            //保存对话框是否记忆上次打开的目录 
            save.RestoreDirectory = true;
            return save;
        }
    }
}
